﻿CREATE PROCEDURE [dbo].[proc_Project_Task_Getlist_Sum]
	(
		@StartDate nvarchar(10),
		@EndDate nvarchar(10),
		@Stext nvarchar(50),
		@DeptId int,
		@WebType smallint,
		@EmpState int,
		@JD smallint,
		@StartIndex int,
		@EndIndex int
	)
AS
Begin
	Declare @sRq Datetime,@eRq Datetime
	Set @sRq = Convert(Datetime,@StartDate+' 00:00:00')
	Set @eRq = Convert(Datetime,@EndDate+' 23:59:59')

	Declare @bmbh_T  varchar(30)
	Select @bmbh_T=bmbh From department Where id=@DeptId

If @WebType<>9
Begin
	;WITH List1 As(Select ROW_NUMBER() OVER (ORDER BY IsFinish,CreateDate Desc,Id Desc)AS Row
		,Id
		,TaskId
		,OrderId
		,ProjectId
		,CusName
		,Title
		,Description
		,DownPersonName
		,ExecPersonName
		,StartDate
		,IsNull(EndDate,'1900-01-01') As EndDate
		,DateDiff(day,StartDate,IsNull(EndDate,GetDate())) As CountDay
		,IsFinish
,b.State
		From Project_Task a,
			(Select UserName,(Case When State<>5 Then 1 Else 2 End) As State From Employee
			 Where DepartmentId In(Select Id From Department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)
			And UserName<>'') b
		Where a.ExecPerson=b.UserName
			And CreateDate>=@sRq And CreateDate<=@eRq
			And (@Stext='' Or CusName like '%'+@Stext+'%')
			And WebType=@WebType
			And (@JD=100 Or IsFinish=@JD)
	),
	List As(Select * From List1
		Where @EmpState =0 Or State = @EmpState
	)

	Select *,(Select Count(0) From List) As RecordCount From list Where Row between @startIndex and @EndIndex Order By Row
End
Else
Begin
	;WITH List1 As(Select ROW_NUMBER() OVER (ORDER BY IsFinish,CreateDate Desc,Id Desc)AS Row
		,Id
		,TaskId
		,OrderId
		,ProjectId
		,CusName
		,Title
		,Description
		,DownPersonName
		,ExecPersonName
		,StartDate
		,IsNull(EndDate,'1900-01-01') As EndDate
		,DateDiff(day,StartDate,IsNull(EndDate,GetDate())) As CountDay
		,IsFinish
,b.State
		From Project_Task a,
			(Select UserName,(Case When State<>5 Then 1 Else 2 End) As State From Employee
			 Where DepartmentId In(Select Id From Department Where Left(bmbh,Len(@bmbh_T))=@bmbh_T)
			And UserName<>'') b
		Where a.ExecPerson=b.UserName
			And CreateDate>=@sRq And CreateDate<=@eRq
			And (@Stext='' Or CusName like '%'+@Stext+'%')
			And WebType In(1,2,3,4,5,6,7,8)
			And (@JD=100 Or IsFinish=@JD)
	),
	List As(Select * From List1
		Where @EmpState =0 Or State = @EmpState
	)

	Select *,(Select Count(0) From List) As RecordCount From list Where Row between @startIndex and @EndIndex Order By Row
End
End
